﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_RegisterTreeRelationsValue')
	BEGIN
		DROP  Procedure  esf_sso.P_RegisterTreeRelationsValue
	END
GO


CREATE Procedure esf_sso.P_RegisterTreeRelationsValue
(
	@pOrderedRelationIDList VARCHAR (MAX) = NULL
	,@pOrderedRelationCodeList VARCHAR (MAX) = NULL
	,@pOrderedBasicReferencesTypeIDsList VARCHAR (MAX) = NULL
	,@pValue VARCHAR (500) = NULL
)
AS
BEGIN

	DECLARE @tblOrderedRelationCodeList TABLE (BasicReferenceID INT, BasicReferenceCode VARCHAR (MAX), BasicReferenceTypeID INT, [Index] INT)

	DECLARE @ID INT
	DECLARE @lastID INT
	DECLARE @code VARCHAR (MAX)
	DECLARE @typeID INT
	DECLARE @index INT


	IF @pOrderedRelationIDList IS NOT NULL
	BEGIN
	
		INSERT INTO @tblOrderedRelationCodeList
		SELECT 
			IDs.BasicReferenceID
			,IDs.BasicReferenceCode
			,IDs.BasicReferenceTypeID
			,IDs.[index]
		FROM
			(SELECT 
				CONVERT (INT, C.tempItem) BasicReferenceID, BR.BasicReferenceTypeID, BR.Code BasicReferenceCode, C.[index] FROM dbo.FUBSplitString (@pOrderedRelationIDList, ',') C
			INNER JOIN
				esf_sso.BasicReference BR ON BR.ID = C.tempItem
			) AS IDs
				
	END
	ELSE IF @pOrderedRelationCodeList IS NOT NULL
	BEGIN
	
		INSERT INTO @tblOrderedRelationCodeList
		SELECT 
			IDs.BasicReferenceID
			,IDs.BasicReferenceCode
			,TypeIDs.TypeID
			,IDs.[index]
		FROM
			(SELECT 
				BR.ID BasicReferenceID, C.tempItem BasicReferenceCode, C.[index] FROM dbo.FUBSplitString (@pOrderedRelationCodeList, ',') C
			INNER JOIN
				esf_sso.BasicReference BR ON BR.Code = C.tempItem
			) AS IDs
		INNER JOIN
			(SELECT CONVERT (INT, tempItem) TypeID, [index] FROM dbo.FUBSplitString (@pOrderedBasicReferencesTypeIDsList, ',')) TypeIDs 
				ON TypeIDs.[index] = IDs.[index]
	
	END
	ELSE
	BEGIN
		RAISERROR ('Must provide IDs or Codes!', 16, 1)
	END

--	print 'Mostrando compilacion de datos de entrada...'
--	SELECT * FROM @tblOrderedRelationCodeList
		
		
	DECLARE curRelations CURSOR FOR
		SELECT BasicReferenceID, BasicReferenceCode, BasicReferenceTypeID, [index] FROM @tblOrderedRelationCodeList
		
	DECLARE @relID INT
	DECLARE @childID INT
	
	DECLARE @inspectQuery VARCHAR(MAX)
	DECLARE @insertQuery VARCHAR(MAX)
	
	SELECT @index = MAX ([index]) FROM @tblOrderedRelationCodeList
	SELECT @ID = BasicReferenceID FROM @tblOrderedRelationCodeList WHERE [index] = (SELECT MAX ([index]) FROM @tblOrderedRelationCodeList)
	
	SET @lastID = @ID
	
	SET @inspectQuery = 'SELECT TOP 1 1 FROM esf_sso.TreeBasicReferenceValue WHERE BasicReferenceID_' + CONVERT (VARCHAR (MAX), @index) + ' = ' + CONVERT (VARCHAR (MAX), @ID)
	SET @insertQuery = 'INSERT INTO esf_sso.TreeBasicReferenceValue VALUES (''' + @pValue + '''' 
	
	
	SET @relID = 0
	
	
	OPEN curRelations
	
	FETCH NEXT FROM curRelations 
		INTO @ID, @code, @typeID, @index
	
	
	WHILE (@@FETCH_STATUS = 0 AND @index < (SELECT MAX ([index]) FROM @tblOrderedRelationCodeList))
    BEGIN
    
		print 'Evaluando index = ' + CONVERT (VARCHAR (MAX), @index)
    
		SET @inspectQuery = @inspectQuery + ' AND BasicReferenceID_' + CONVERT (VARCHAR (MAX), @index) + ' = ' + CONVERT (VARCHAR (MAX), @ID)
    
		SET @insertQuery = @insertQuery + ', ' + CONVERT (VARCHAR (MAX), @ID)
    
		IF @index = 0
		BEGIN
			IF NOT EXISTS (SELECT TOP 1 1 from [esf_sso].[TreeBasicReference] WHERE ParentBasicReferenceID = @ID)
			BEGIN
			
				INSERT INTO esf_sso.TreeBasicReference (ParentBasicReferenceID, BasicReferenceID) VALUES (@ID, NULL)
			
			END
		END
    
		SELECT @childID = BasicReferenceID FROM @tblOrderedRelationCodeList WHERE [Index] = @index + 1
    
		print 'ParentID = ' + CONVERT (VARCHAR (MAX), @ID) + ' con ChildID = ' + CONVERT (VARCHAR (MAX), @childID)
    
		SET @relID = NULL
    
		SELECT 
			@relID = ID FROM [esf_sso].[TreeBasicReference] 
		WHERE 
			ParentBasicReferenceID = @ID 
			AND BasicReferenceID = @childID
    
		print 'relID encontrado inicialmente: ' + CONVERT (VARCHAR (MAX), @relID)
    
		IF @relID IS NULL
		BEGIN
			
			print 'Relacion no encontrada creando...'
			
			EXEC esf_sso.P_InsertTreeBasicReferenceIDs @ID, @childID, @relID OUT
			
		END
		
		print 'RelId = ' + CONVERT (VARCHAR (MAX), @relID)
    
		FETCH NEXT FROM curRelations 
			INTO @ID, @code, @typeID, @index
    
    END
	
	
	CLOSE curRelations
	DEALLOCATE curRelations
	
	print 'Updating with value = ' + @pValue + ', RelId = ' + CONVERT (VARCHAR (MAX), @relID)
	
	UPDATE 
		esf_sso.TreeBasicReference
	SET 
		Value = @pValue
	WHERE
		ID = @relID
	
	SET @insertQuery = @insertQuery + ', ' + CONVERT (VARCHAR (MAX), @lastID) + ')'
	
	
	DECLARE @query VARCHAR (MAX)
	
	
	SET @query = 'IF NOT EXISTS (' + @inspectQuery + ')' + @insertQuery 
		
--	print @query
	EXEC (@query)

	
END